<?php
class FileUploadController extends AppController {
    public function index()
    {
        include("Component/connection.php");
        if($this->Session->read("login")=="yes")
        {
            if($this->request->is('post'))
            {
                //extension restrictions on upload
                $allowedExts = array("csv", "xls");
                $extpart=explode(".", $_FILES["file"]["name"]);
                $extension = end($extpart);
                // $this->FileUpload->upload();
                if(in_array($extension,$allowedExts))
                {
                    if ($_FILES["file"]["error"] > 0)
                    {
                        $this->Session->setFlash(__("Error: " . $_FILES["file"]["error"] . "<br>"));
                    }
                    else
                    {
                        $this->Session->setFlash(__("Upload: " . $_FILES["file"]["name"] . "<br>
                    \n Type: " . $_FILES["file"]["type"] . "<br>
                    \n Size: " . ($_FILES["file"]["size"] / 1024) . " kB<br>
                    \n Temp file: " . $_FILES["file"]["tmp_name"] . "<br>
                    "));
                        // move the file from temp to webroot/files/
                       /* if (file_exists(WWW_ROOT.'files/data.csv'))
                        {
                            $this->Session->setFlash(__("Data files already exists. Please delete the data.csv file in webroot/files before uploading a new data file."));
                            $this->redirect(array(
                                'controller'=>'fileupload',
                                'action'=>'index'));
                        }*/
                        //else
                        //{
                            $uploaddir = WWW_ROOT.'files/';
                            $uploadfile = $uploaddir . "data." . $extension;
                            move_uploaded_file($_FILES["file"]["tmp_name"], $uploadfile);
                            $this->Session->setFlash(__("Stored in: " . "webroot/files/data.csv"));
                        //}

                        $this->redirect(array(
                            'controller'=>'fileupload',
                            'action'=>'readData'
                        ));
                    }
                }
                else
                {
                    $this->Session->setFlash(__("Invalid File.Only a .CSV file or a .XLS file is allowed"));
                }
            }
        }
    }

    public function readdata()
    {
        include("Component/connection.php");
        if($this->Session->read("login")=="yes")
        {
            if($this->request->is('post') && isset($this->request->data['updatedb']))
            {
                //redirect to new page
                $this->redirect(array(
                    //redirected to this controller
                    'controller'=>'fileupload',
                    //redirected to controller's function
                    'action'=>'updatedb'
                ) );
            }
            //clear everything in the session except the login info.

            $login=$this->Session->read("login");
            $loginname=$this->Session->read("UserName");
            $this->Session->destroy();
            $this->Session->write("login",$login);
            $this->Session->write("UserName",$loginname);

            set_time_limit(0);

            //read from data.csv file
            $file_handle = fopen(WWW_ROOT."files/data.csv","r");
            $linecount=0;
            $cellcount=0;

            while(!feof($file_handle))
            {
                $cellperline=0;
                $data=fgets($file_handle);
                $listing=explode(",",$data);
                $linecount++;
                foreach($listing as $key => $value)
                {
                    $cellperline++;
                }
                foreach($listing as $key => $value)
                {
                    $cellcount++;

                    if($linecount==1)
                    {
                        $column=$cellcount;
                    }
                    else
                    {
                        $column=$cellcount-(($linecount-1)*$cellperline);
                    }

                    //get the type of the data file
                    if($linecount==1 && $cellcount==1)
                    {
                        $this->Session->write('ulType',$value);
                    }
                    //get the data
                    else
                    {
                        if($value!='-')
                        {
                            $this->Session->write("cell[".$linecount.",".$column."]",$value);
                        }
                            //$this->Session->write("cell[".$linecount.",".$column."]",$value);

                    }
                }
            }
            $this->Session->write("ullines",$linecount);
            $this->Session->write("ulcols",$column);

            fclose($file_handle);
        }
    }

    public function updatedb()
    {
        include("Component/connection.php");
        if($this->Session->read("login")=="yes")
        {
            $this->layout='custom'; //app/views/layouts/custom.ctp
            if($this->request->is('post') && isset($this->request->data['calculation']))
            {
                //redirect to new page
                $this->redirect(array(
                    //redirected to this controller
                    'controller'=>'Calculations',
                    //redirected to controller's function
                    'action'=>'index'
                ) );
            }
            //read from session, get the info of fund
            //then search the apir code for the fund
            //then insert into the db

            //get the total funds number and total record number
            set_time_limit(0);
            if($this->Session->read("ulType")=="Fund")
            {
                $totalcols=$this->Session->read("ulcols");
                $totalRow=$this->Session->read("ullines");
                for ($n=2;$n<=$totalcols;$n+=1)
                {
                    //get the fund name
                    $fundname=$this->Session->read("cell[2,".$n."]");
                    //find apir
                    $result=$this->FileUpload->getAPIR($fundname." Fund");
                    foreach($result as $re)
                    {
                        foreach($re as $r)
                        {
                            foreach($r as $rr)
                            {
                                //write apir into session
                                $this->Session->write("FundApir[2,".$n."]",$rr);
                            }
                        }
                    }
                }

                //loop through to insert record
                $totalInsert=0;
                $totalUpdate=0;
                $totalIgnore=0;
                for($line=3;$line<=$totalRow;$line++)
                {
                    for($col=2;$col<=$totalcols;$col++)
                    {
                        $name=$this->Session->read("cell[2,".$col."]");
                        $apir=$this->Session->read("FundApir[2,".$col."]");
                        $dateOriginal=$this->Session->read("cell[".$line.",1]");
                        $timestamp=strtotime($dateOriginal);

                        $valueOld=0;
                        if($timestamp == false)
                        {
                            $timestamp=strtotime(str_replace('/','-',$dateOriginal));
                        }
                        $date=date("Y-m-d", $timestamp);
                        $value=$this->Session->read("cell[".$line.",".$col."]");

                        $this->loadModel('Managedfund');
                        $searchResult=$this->Managedfund->findByFundnameAndDate($name,$date);
                        //debug($searchResult);

                        if( !$searchResult && $value!=0)
                        {
                            //insert new record
                            $this->FileUpload->insertIntoDB($name,$apir,$date,$value);
                            $this->insertModel($name,$date);
                            $totalInsert++;
                            $this->Session->write("insertName[".$totalInsert."]",$name);
                            $this->Session->write("insertDate[".$totalInsert."]",$date);
                            $this->Session->write("insertValue[".$totalInsert."]",$value);
                        }
                        else
                        {
                            foreach($searchResult as $searchRe)
                            {
                                $n=0;
                                foreach($searchRe as $searchR)
                                {
                                    $n++;
                                    if($n==6)
                                    {
                                        $valueOld=$searchR;
                                    }
                                }
                            }
                            if($valueOld!=$value && $value!=0)
                            {
                                //update record
                                $this->FileUpload->updateDB($name,$date,$value);
                                $totalUpdate++;
                                $this->Session->write("updateName[".$totalUpdate."]",$name);
                                $this->Session->write("updateDate[".$totalUpdate."]",$date);
                                $this->Session->write("updateValue[".$totalUpdate."]",$value);
                            }
                            else
                            {
                                if($value!=0)
                                {
                                    $totalIgnore++;
                                }
                            }
                        }
                        $this->FileUpload->commit();
                    }
                }
                $this->Session->write("totalUpdate",$totalUpdate);
                $this->Session->write("totalInsert",$totalInsert);
                $this->Session->write("totalIgnore",$totalIgnore);
            }
            else if ($this->Session->read("ulType")=="Index")
            {
                $totalcols=$this->Session->read("ulcols");
                $totalRow=$this->Session->read("ullines");

                //loop through to insert record
                $totalInsert=0;
                $totalUpdate=0;
                $totalIgnore=0;
                for($line=3;$line<=$totalRow;$line++)
                {
                    for($col=2;$col<=$totalcols;$col++)
                    {
                        $name=$this->Session->read("cell[2,".$col."]");
                        $dateOriginal=$this->Session->read("cell[".$line.",1]");
                        $timestamp=strtotime($dateOriginal);

                        $valueOld=0;
                        if($timestamp == false)
                        {
                            $timestamp=strtotime(str_replace('/','-',$dateOriginal));
                        }
                        $date=date("Y-m-d", $timestamp);
                        $value=$this->Session->read("cell[".$line.",".$col."]");

                        $this->loadModel('Index');
                        $searchResult=$this->Index->findByIndexnameAndDate($name,$date);
                        //debug($searchResult);

                        if( !$searchResult && $value!=0)
                        {
                            //insert new record
                            $this->FileUpload->insertIntoDBindex($name,$date,$value);
                            $this->insertModel($name,$date);
                            $totalInsert++;
                            $this->Session->write("insertName[".$totalInsert."]",$name);
                            $this->Session->write("insertDate[".$totalInsert."]",$date);
                            $this->Session->write("insertValue[".$totalInsert."]",$value);
                        }
                        else
                        {
                            foreach($searchResult as $searchRe)
                            {
                                $n=0;
                                foreach($searchRe as $searchR)
                                {
                                    $n++;
                                    if($n==6)
                                    {
                                        $valueOld=$searchR;
                                    }
                                }
                            }
                            if($valueOld!=$value && $value!=0)
                            {
                                //update record
                                $this->FileUpload->updateDBindex($name,$date,$value);
                                $totalUpdate++;
                                $this->Session->write("updateName[".$totalUpdate."]",$name);
                                $this->Session->write("updateDate[".$totalUpdate."]",$date);
                                $this->Session->write("updateValue[".$totalUpdate."]",$value);
                            }
                            else
                            {
                                if($value!=0)
                                {
                                    $totalIgnore++;
                                }
                            }
                        }
                    }
                }
                $this->Session->write("totalUpdate",$totalUpdate);
                $this->Session->write("totalInsert",$totalInsert);
                $this->Session->write("totalIgnore",$totalIgnore);
            }
        }
    }

    private function insertModel($finame,$fidate)
    {
        include("Component/connection.php");
        $sql="select distinct portfolioname from portfoliomanagement where fundlist like '%".$finame."%';";
        $portfolioname=array();
        $list=array();
        $result=mysqli_query($con,$sql);
        foreach($result as $resul)
        {
            foreach($resul as $resu)
            {
                //echo $resu."<br>";
                array_push($portfolioname,$resu);
            }
        }
        //debug($portfolioname);
        if(sizeof($portfolioname)>0)
        {
            for($n=0;$n<sizeof($portfolioname);$n++)
            {
                $sql2="select count(portfoliosid) from modelportfolios where portfolioname='".$portfolioname[$n]."' and year(date)=year('".$fidate."') and month(date)=month('".$fidate."');";
                $result=mysqli_query($con,$sql2);
                $noofrecord=0;
                foreach($result as $resul)
                {
                    foreach($resul as $resu)
                    {
                        $noofrecord=$resu;
                    }
                }
                if($noofrecord==0)
                {
                    $datearray=array();
                    $sql4="select date from portfoliomanagement where portfolioname='".$portfolioname[$n]."' order by date asc;";
                    $result3=mysqli_query($con,$sql4);
                    foreach($result3 as $resul)
                    {
                        foreach($resul as $resu)
                        {
                            array_push($datearray,$resu);
                        }
                    }
                    if(sizeof($datearray)==1)
                    {
                        $date=$datearray[0];
                    }
                    else
                    {
                        for($m=0;$m<sizeof($datearray);$m++)
                        {
                            if($m<(sizeof($datearray)-1))
                            {
                                $datebegin=$datearray[$m];
                                $dateend=$datearray[$m+1];
                                if(strtotime($datebegin)<=strtotime($fidate) && strtotime($fidate)<strtotime($dateend))
                                {
                                    $date=$datebegin;
                                }
                            }
                            else
                            {
                                $date=$datearray[sizeof($datearray)-1];
                            }
                        }
                    }
                    $sql3="select fundlist,indexlist,fweightinglist,iweightinglist from portfoliomanagement where portfolioname='".$portfolioname[$n]."' and date ='".$date."';";
                    $result2=mysqli_query($con,$sql3);
                    foreach($result2 as $resul)
                    {
                        foreach($resul as $resu)
                        {
                            array_push($list,$resu);
                        }
                    }
                    $sql4="insert into modelportfolios(portfolioname,fundslist,indexlist,date,fweightinglist,iweightinglist) values ('".$portfolioname[$n]."','".$list[0]."','".$list[1]."','".$fidate."','".$list[2]."','".$list[3]."')";
                    mysqli_query($con,$sql4);

                    $sql3="select portfolioname,fundlist,indexlist,fweightinglist,iweightinglist from portfoliomanagement where fundlist like '%".$portfolioname[$n]."%' and date ='".$date."';";
                    $result2=mysqli_query($con,$sql3);
                    $list2=array();
                    foreach($result2 as $resul)
                    {
                        foreach($resul as $resu)
                        {
                            array_push($list2,$resu);
                        }
                    }
                    $sql4="insert into modelportfolios(portfolioname,fundslist,indexlist,date,fweightinglist,iweightinglist) values ('".$list2[0]."','".$list2[1]."','".$list2[2]."','".$fidate."','".$list2[3]."','".$list2[4]."')";
                    mysqli_query($con,$sql4);
                }
                
            }
        }
    }
}